IF OBJECT_ID('ssf.ddl_rename_table') IS NOT NULL
	DROP PROCEDURE ssf.ddl_rename_table
GO

CREATE PROCEDURE ssf.ddl_rename_table
	@current_table_name NVARCHAR(512)
	, @new_table_name NVARCHAR(512)
	, @backup_fl BIT = 0
	, @override_backup_fl BIT = 0
AS
/*

*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@sql_error INT
			, @sql_error2 INT
			, @row_count BIGINT
			, @sql VARCHAR(MAX)
			, @nsql NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @two_quotes CHAR(2) = CHAR(39) + CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@current_db_name NVARCHAR(128) = ISNULL(PARSENAME(@current_table_name, 3), DB_NAME())
			, @current_schema_name NVARCHAR(128) = ISNULL(PARSENAME(@current_table_name, 2), SCHEMA_NAME())
			, @current_table_local_name NVARCHAR(128) = ISNULL(PARSENAME(@current_table_name, 1), '')
			, @new_db_name NVARCHAR(128) = ISNULL(PARSENAME(@new_table_name, 3), DB_NAME())
			, @new_schema_name NVARCHAR(128) = ISNULL(PARSENAME(@new_table_name, 2), SCHEMA_NAME())
			, @new_table_local_name NVARCHAR(128) = ISNULL(PARSENAME(@new_table_name, 1), '')
			, @backup_suffix NVARCHAR(128) = '_bkp'
			, @exists_fl BIT = NULL
			, @backup_exists_fl BIT = NULL
-- ============

-- ============
/*<code_comment><![CDATA[fail if db and schema are not identical]]></code_comment>*/
IF @current_db_name <> @new_db_name
OR @current_schema_name <> @new_schema_name
BEGIN
	RAISERROR('Cannot rename <%s> to <%s>, database and schema must be identical.', 16, 1, @current_table_name, @new_table_name)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[test current table existence]]></code_comment>*/
EXECUTE ssf.ddl_exists_table
	@table_name = @current_table_name
	-- NVARCHAR(512)
	, @exists_fl = @exists_fl OUTPUT
	-- BIT OUTPUT

IF @exists_fl = 0
BEGIN
	RAISERROR('Table not found: <%s>.', 16, 1, @current_table_name)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[BEGIN: if the new table already exists, and is different from current table]]></code_comment>*/
EXECUTE ssf.ddl_exists_table
	@table_name = @new_table_name
	-- NVARCHAR(512)
	, @exists_fl = @exists_fl OUTPUT
	-- BIT OUTPUT

IF @exists_fl = 1
AND @current_table_local_name <> @new_table_local_name
BEGIN
	-- ============
	/*<code_comment><![CDATA[BEGIN: if a backup is required]]></code_comment>*/
	IF @backup_fl = 1
	BEGIN
		IF LEN(@new_table_local_name) > (128 - LEN(@backup_suffix))
			RAISERROR('Table name is too long to append backup suffix.', 16, 1)
		
		DECLARE @backup_table_name NVARCHAR(512) = QUOTENAME(@new_db_name) + '.' + QUOTENAME(@new_schema_name) + '.' + QUOTENAME(@new_table_local_name + @backup_suffix)
		
		-- ============
		/*<code_comment><![CDATA[fail if the backup already exists and cannot be overridden]]></code_comment>*/
		EXECUTE ssf.ddl_exists_table
			@table_name = @backup_table_name
			-- NVARCHAR(512)
			, @exists_fl = @exists_fl OUTPUT
			-- BIT OUTPUT

		IF @exists_fl = 1
		AND @override_backup_fl = 0
		BEGIN
			RAISERROR('Cannot rename without backup overriding existing table: <%s>.', 16, 1, @backup_table_name)
			RETURN 1
		END
		-- ============

		-- ============
		/*<code_comment><![CDATA[backup the existing table]]></code_comment>*/
		EXECUTE ssf.ddl_rename_table
			@current_table_name = @new_table_name
			-- NVARCHAR(512)
			, @new_table_name = @backup_table_name
			-- NVARCHAR(512)
			, @backup_fl = 0
			-- BIT
		-- ============
	END
	/*<code_comment><![CDATA[END: if a backup is required]]></code_comment>*/
	-- ============
	
	-- ============
	/*<code_comment><![CDATA[drop the existing table]]></code_comment>*/
	EXECUTE ssf.ddl_drop_table
		@table_name = @new_table_name
		-- NVARCHAR(512)
	-- ============
END
/*<code_comment><![CDATA[END: if the nex table name already exists]]></code_comment>*/
-- ============

-- ============
/*<code_comment><![CDATA[rename table]]></code_comment>*/
SET @nsql = 'EXECUTE @sql_error = ' + QUOTENAME(@current_db_name) + '.sys.sp_rename'
	+ @crlf + @tab + '@objname = ' + QUOTENAME(QUOTENAME(@current_schema_name) + '.' + QUOTENAME(@current_table_local_name), @quote)
	+ @crlf + @tab + '-- NVARCHAR(1035)'
	+ @crlf + @tab + ', @newname = ' + QUOTENAME(@new_table_local_name, @quote)
	+ @crlf + @tab + '-- NVARCHAR(128)'
	+ @crlf + @tab + ', @objtype = ' + @quote + 'OBJECT' + @quote
	+ @crlf + @tab + '-- VARCHAR(13)'

PRINT @nsql

EXECUTE @sql_error2 = sp_executesql
	@nsql
	, N'@sql_error INT OUTPUT'
	, @sql_error OUTPUT

IF ISNULL(@sql_error, 1) <> 0
OR ISNULL(@sql_error2, 1) <> 0
BEGIN
	RAISERROR('Failed to rename table <%s> to <%s>.', 16, 1, @current_table_name, @new_table_name)
	RETURN 1
END
-- ============

RETURN @@ERROR
GO
